﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXShop.IDAL.Accessories;
using System.Data;
using System.Data.SqlClient;

namespace YXShop.SQLServerDAL.Accessories
{
    public class Question:IQuestion
    {

        #region "Database Operation"
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(YXShop.Model.Accessories.Question model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [yxs_question](");
            strSql.Append("[questionId],[questionContent],[questionType],[questionDiscription],[necessarily],[result],[investigateStateDate],[investigateEndDate],[questionArea])");
            strSql.Append("values(");
            strSql.Append("@questionId,@questionContent,@questionType,@questionDiscription,@necessarily,@result,@investigateStateDate,@investigateEndDate,@questionArea ) Select scope_IDENTITY()");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParams(model);
            object obj = ChangeHope.DataBase.SQLServerHelper.GetSingle(strSql.ToString(),parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        public void Update(YXShop.Model.Accessories.Question model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [yxs_question] set ");
            strSql.Append("[questionId]=@questionId,[questionContent]=@questionContent,[questionType]=@questionType,[questionDiscription]=@questionDiscription,[necessarily]=@necessarily,[result]=@result,[investigateStateDate]=@investigateStateDate,[investigateEndDate]=@investigateEndDate,[questionArea]=@questionArea  ");
            strSql.Append("where [id]=@id ");
            SqlParameter[] parameters = (SqlParameter[])this.ValueParams(model);
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString(),parameters);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_question] where [id]="+id+"");
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="ids"></param>
        public void DeleteAll(string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [yxs_question] where [id] in(");
            strSql.Append(ids+" )");
            ChangeHope.DataBase.SQLServerHelper.ExecuteSql(strSql.ToString());
        } 
        #endregion

        #region  "Data load"
        /// <summary>
        /// 根据ID返回对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YXShop.Model.Accessories.Question GetModelById(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 [id],[questionId],[questionContent],[questionType],[questionDiscription],[necessarily],[result],[investigateStateDate],[investigateEndDate],[questionArea] from [yxs_question] ");
            strSql.Append("where [id]=@id");
            SqlParameter[] parameters ={
                                         new SqlParameter("@id",SqlDbType.Int,4) 
                                    };
            parameters[0].Value = id;
            YXShop.Model.Accessories.Question model = null;
            using(SqlDataReader reader=ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString(),parameters)){
                while(reader.Read()){
                    model = new YXShop.Model.Accessories.Question();
                    model.Id=(int)reader["id"];
                    model.QuestionId = (int)reader["questionId"];
                    model.QuestionContent = (string)reader["questionContent"];
                    model.QuestionType = Convert.ToInt32(reader["questionType"]);
                    model.QuestionDiscription = (string)reader["questionDiscription"];
                    model.Necessarily = Convert.ToInt32(reader["necessarily"]);
                    model.Result = (string)reader["result"];
                    model.InvestigateStateDate = Convert.ToDateTime(reader["investigateStateDate"]);
                    model.InvestigateEndDate = Convert.ToDateTime(reader["investigateEndDate"]);
                    model.QuestionArea = (string)reader["questionArea"];
                }
            }
            return model;
        }

        /// <summary>
        /// 得到指定条件的列表
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<YXShop.Model.Accessories.Question> GetAll(string strWhere)
        {
            List<YXShop.Model.Accessories.Question> list = new List<YXShop.Model.Accessories.Question>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  [id],[questionId],[questionContent],[questionType],[questionDiscription],[necessarily],[result],[investigateStateDate],[investigateEndDate],[questionArea] from [yxs_question]  ");
            if (strWhere != null && strWhere != "")
            {
                strSql.Append("where " + strWhere + " ");
            }
            using (SqlDataReader reader = ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString()))
            {
                while (reader.Read())
                {
                    YXShop.Model.Accessories.Question model = new YXShop.Model.Accessories.Question();
                    model.Id = (int)reader["id"];
                    model.QuestionId = (int)reader["questionId"];
                    model.QuestionContent = (string)reader["questionContent"];
                    model.QuestionType = Convert.ToInt32(reader["questionType"]);
                    model.QuestionDiscription = (string)reader["questionDiscription"];
                    model.Necessarily = Convert.ToInt32(reader["necessarily"]);
                    model.Result = reader["result"].ToString();
                    model.InvestigateStateDate = Convert.ToDateTime(reader["investigateStateDate"]);
                    model.InvestigateEndDate = Convert.ToDateTime(reader["investigateEndDate"]);
                    model.QuestionArea = (string)reader["questionArea"];
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <returns></returns>
        public ChangeHope.DataBase.DataByPage GetList(int questID)
        {
            string sql = "[select] * [from] yxs_question [where] 1=1 and questionId="+questID+" [order by] id asc";
            ChangeHope.DataBase.DataByPage datapage = new ChangeHope.DataBase.DataByPage();
            datapage.Sql = sql;
            datapage.GetRecordSetByPage();
            return datapage;
        }

        #endregion

        #region "Other Faction"
        public IDataParameter[] ValueParams(YXShop.Model.Accessories.Question model)
        {
            SqlParameter[] parameters=new SqlParameter[10];
            parameters[0] = new SqlParameter("@id",SqlDbType.Int,4);
            parameters[1] = new SqlParameter("@questionId",SqlDbType.Int,4);
            parameters[2] = new SqlParameter("@questionContent",SqlDbType.VarChar,200);
            parameters[3] = new SqlParameter("@questionType",SqlDbType.Int,4);
            parameters[4] = new SqlParameter("@questionDiscription",SqlDbType.VarChar,2000);
            parameters[5] = new SqlParameter("@necessarily",SqlDbType.Int,4);
            parameters[6] = new SqlParameter("@result",SqlDbType.VarChar,50);
            parameters[7] = new SqlParameter("@investigateStateDate",SqlDbType.DateTime,8);
            parameters[8] = new SqlParameter("@investigateEndDate",SqlDbType.DateTime,8);
            parameters[9] = new SqlParameter("@questionArea",SqlDbType.VarChar,50);
            parameters[0].Value = model.Id;
            parameters[1].Value = model.QuestionId;
            parameters[2].Value = model.QuestionContent;
            parameters[3].Value = model.QuestionType;
            parameters[4].Value = model.QuestionDiscription;
            parameters[5].Value = model.Necessarily;
            parameters[6].Value = model.Result;
            parameters[7].Value = model.InvestigateStateDate;
            parameters[8].Value = model.InvestigateEndDate;
            parameters[9].Value = model.QuestionArea;
            return parameters;
        }
        #endregion
    }
}
